package superhero.data;

import java.io.FileNotFoundException;
import java.io.IOException;
import com.csvreader.CsvReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jimsonm0466
 */
public class CreateSuperheroDB
{
    private static String factionID, characterName, realName, powers;

    public static void main(String[] args)
    {
        createCharacterTable();
    }
    
    public static void createCharacterTable()
    {
        try
        {
            final String DB_URL =
                    "jdbc:derby:SuperheroDB;create=true";

            Connection conn =
                    DriverManager.getConnection(DB_URL);

            Statement stmt = conn.createStatement();

            stmt.execute("CREATE TABLE Characters ("
                    + "FactionID CHAR(4) , "
                    + "CharacterName CHAR(25) NOT NULL PRIMARY KEY, "
                    + "RealName CHAR(45), " + "Powers VARCHAR(200))");

            CsvReader characters = new CsvReader("CharacterTable.csv");
            CsvReader groups = new CsvReader("FactionTable.csv");

            String sql;

            characters.readHeaders();

            while (characters.readRecord())
            {
                factionID = characters.get("Faction ID");
                characterName = characters.get("Character").replace('\'', '~');
                realName = characters.get("Real Name").replace('\'', '~');
                powers = characters.get("Powers").replace('\'', '~');

                try
                {
                    sql = "INSERT INTO Characters VALUES"
                            + "('" + factionID + "', '" + characterName + "', '" +
                            realName + "', '" + powers + "')";
                    
                    stmt.executeUpdate(sql);

                }
                catch (Exception ex)
                {
                    System.out.println("ERROR: " + ex.getMessage());
                    System.err.println("Database was not created!");
                }

            }
            System.out.println("Database is completed!");
            characters.close();
            
            stmt.execute("CREATE TABLE Factions ("
                    + "FactionID CHAR(4) PRIMARY KEY , "
                    + "FactionName CHAR(25))");
            
            groups.readHeaders();

            while (groups.readRecord())
            {
                String factionID = groups.get("Faction ID");
                String factionName = groups.get("Faction").replace('\'', '~');
                

                try
                {
                    sql = "INSERT INTO Factions VALUES"
                            + "('" + factionID + "', '" + factionName + "')";

                    
                    stmt.executeUpdate(sql);

                }
                catch (Exception ex)
                {
                    System.out.println("ERROR: " + ex.getMessage());
                }

            }
            System.out.println("Database finished!");
            groups.close();

        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(CreateSuperheroDB.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
        }

        }
        
}